﻿using Dapper;
using HR.IServices.PengIService;
using HR.Models.PengModels;
using System.Data;
using System.Data.SqlClient;

namespace HR.Services.PengService
{
    public class RenLiZiYuanGuanLiServices : IRenLiZiYuanGuanLiServices
    {
        private readonly IConfiguration conf;
        private readonly string? conStr;

        public RenLiZiYuanGuanLiServices(IConfiguration conf)
        {
            this.conf = conf;
            conStr = this.conf["ConnectionStrings"];
        }
        public Task<int> Add(RenLiZiYuanDangAnGuanLi record)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    //return con.Execute($"insert into dbo.human_file(human_id, first_kind_id, first_kind_name, second_kind_id, second_kind_name,third_kind_id, third_kind_name, human_name, human_address, human_postcode, human_pro_designation,human_major_kind_id, human_major_kind_name, human_major_id, hunma_major_name, human_telephone,human_mobilephone, human_bank, human_account, human_qq, human_email, human_hobby, human_speciality,human_sex, human_religion, human_party, human_nationality, human_race, human_birthday,human_birthplace, human_age, human_educated_degree, human_educated_years, human_educated_major,human_society_security_id, human_id_card, remark, salary_standard_id, salary_standard_name,salary_sum, demand_salaray_sum, paid_salary_sum,human_histroy_records, human_family_membership,human_picture,check_status, register, regist_time, human_file_status,attachment_name) values('{record.Human_id}','{record.First_kind_id}','{record.First_kind_name}','{record.Second_kind_id}','{record.Second_kind_name}','{record.Third_kind_id}','{record.Third_kind_name}','{record.Human_name}','{record.Human_address}','{record.Human_postcode}','{record.Human_pro_designation}','{record.Human_major_kind_id}','{record.Human_major_kind_name}','{record.Human_major_id}','{record.Human_major_name}','{record.Human_telephone}','{record.Human_mobilephone}','{record.Human_bank}','{record.Human_account}','{record.Human_qq}','{record.Human_email}','{record.Human_hobby}','{record.Human_speciality}','{record.Human_sex}','{record.Human_religion}','{record.Human_party}','{record.Human_nationality}','{record.Human_race}','{record.Human_birthday}','{record.Human_birthplace}',{record.Human_age},'{record.Human_educated_degree}','{record.Human_educated_years}','{record.Human_educated_major}','{record.Human_society_security_id}','{record.Human_id_card}','{record.Remark}','{record.Salary_standard_id}','{record.Salary_standard_name}','{record.Salary_sum}','{record.Demand_salaray_sum}','{record.Paid_salary_sum}','{record.Human_histroy_records}','{record.Human_family_membership}','{record.Human_picture}','{record.Check_status}','{record.Register}','{record.Regist_time}','{record.Human_file_status}','{record.Attachment_name}')");
                    return con.Execute($"insert into dbo.human_file(human_id,major_change_amount, first_kind_id, first_kind_name, second_kind_id, second_kind_name,third_kind_id, third_kind_name, human_name, human_address, human_postcode, human_pro_designation,human_major_kind_id, human_major_kind_name, human_major_id, human_major_name, human_telephone,human_mobilephone, human_bank, human_account, human_qq, human_email, human_hobby, human_speciality,human_sex, human_religion, human_party, human_nationality, human_race, human_birthday,human_birthplace, human_age, human_educated_degree, human_educated_years, human_educated_major,human_society_security_id, human_id_card, remark, salary_standard_id, salary_standard_name,salary_sum, demand_salaray_sum, paid_salary_sum,human_histroy_records, human_family_membership,human_picture,register, regist_time, human_file_status,attachment_name,check_status) values('{record.Human_id}','{record.Major_change_amount}','{record.First_kind_id}','{record.First_kind_name}','{record.Second_kind_id}','{record.Second_kind_name}','{record.Third_kind_id}','{record.Third_kind_name}','{record.Human_name}','{record.Human_address}','{record.Human_postcode}','{record.Human_pro_designation}','{record.Human_major_kind_id}','{record.Human_major_kind_name}','{record.Human_major_id}','{record.Human_major_name}','{record.Human_telephone}','{record.Human_mobilephone}','{record.Human_bank}','{record.Human_account}','{record.Human_qq}','{record.Human_email}','{record.Human_hobby}','{record.Human_speciality}','{record.Human_sex}','{record.Human_religion}','{record.Human_party}','{record.Human_nationality}','{record.Human_race}','{record.Human_birthday}','{record.Human_birthplace}',{record.Human_age},'{record.Human_educated_degree}','{record.Human_educated_years}','{record.Human_educated_major}','{record.Human_society_security_id}','{record.Human_id_card}','{record.Remark}','{record.Salary_standard_id}','{record.Salary_standard_name}','{record.Salary_sum}','{record.Demand_salaray_sum}','{record.Paid_salary_sum}','{record.Human_histroy_records}','{record.Human_family_membership}','{record.Human_picture}','{record.Register}','{record.Regist_time}','{record.Human_file_status}','{record.Attachment_name}','{record.Check_status}')");
                }
            });
        }

        public Task<List<RenLiZiYuanDangAnGuanLi>> DangAnFind(string? one, string? two, string? three, string? name, string? type, string? minCheng, string? time1, string? time2)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<RenLiZiYuanDangAnGuanLi>($"select * from human_file where human_file_status=1 and check_status=1 and first_kind_name='{one}' and second_kind_name='{two}' and third_kind_name='{three}' and human_name='{name}' and human_major_kind_name='{type}' and hunma_major_name='{minCheng}' and regist_time between '{time1}' and '{time2}'").ToList();
                }
            });
        }

        public Task<int> Delete(int huf_id)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"delete from human_file where huf_id='{huf_id}'");
                }
            });
        }

        public Task<FenYe<RenLiZiYuanDangAnGuanLi>> FenYe(int size, int currentPage)
        {
            return Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", size);
                    dynamicParameters.Add("@keyName", "huf_id");
                    dynamicParameters.Add("@tabelName", "human_file");
                    dynamicParameters.Add("@where", "check_status=0");
                    dynamicParameters.Add("@currentPage", currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<RenLiZiYuanDangAnGuanLi> list = conn.Query<RenLiZiYuanDangAnGuanLi>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<RenLiZiYuanDangAnGuanLi> fenYe = new FenYe<RenLiZiYuanDangAnGuanLi>()
                    {
                        Rows = rows,
                        RenLi = list
                    };
                    return fenYe;
                }
            });
        }

        public Task<List<object>> FindId(int id)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from human_file where huf_id='{id}'";
                    return con.Query<object>(sql).ToList();
                }
            });
        }

        public Task<List<salary_standard>> FindXinChou()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<salary_standard>($"select standard_id,standard_name,salary_sum from salary_standard where check_status=1").ToList();
                }
            });
        }

        public Task<FenYe<RenLiZiYuanDangAnGuanLi>> FuHeFenYe(int size, int currentPage)
        {
            return Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", size);
                    dynamicParameters.Add("@keyName", "huf_id");
                    dynamicParameters.Add("@tabelName", "human_file");
                    //条件查询check_status : 复核状态  为0的
                    dynamicParameters.Add("@where", "human_file_status=1 and check_status=1");
                    dynamicParameters.Add("@currentPage", currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<RenLiZiYuanDangAnGuanLi> list = conn.Query<RenLiZiYuanDangAnGuanLi>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<RenLiZiYuanDangAnGuanLi> fenYe = new FenYe<RenLiZiYuanDangAnGuanLi>()
                    {
                        Rows = rows,
                        RenLi = list
                    };
                    return fenYe;
                }
            });
        }

        public Task<List<RenLiZiYuanDangAnGuanLi>> HuiFuFind(string? one, string? two, string? three, string? name, string? type, string? minCheng, string? time1, string? time2)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<RenLiZiYuanDangAnGuanLi>($"select * from human_file where delete_status=1 and u_roleID!=1 and check_status=1 and first_kind_name='{one}' and second_kind_name='{two}' and third_kind_name='{three}' and human_name='{name}' and human_major_kind_name='{type}' and human_major_name='{minCheng}' and regist_time between '{time1}' and '{time2}'").ToList();
                }
            });
        }

        public Task<int> HuiFuStatus(RenLiZiYuanDangAnGuanLi renLiZiYuanDangAnGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update human_file set human_name='{renLiZiYuanDangAnGuanLi.Human_name}',recovery_time='{renLiZiYuanDangAnGuanLi.Recovery_time}',delete_status='{renLiZiYuanDangAnGuanLi.Delete_status}' where huf_id='{renLiZiYuanDangAnGuanLi.Huf_id}'");
                }
            });
        }

        public Task<List<object>> JianLiID(int id)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from engage_resume where res_id='{id}'";
                    return con.Query<object>(sql).ToList();
                }
            });
        }

        public Task<List<RenLiZiYuanDangAnGuanLi>> SelQuanBu()
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = $"select * from human_file where human_file_status=1 and check_status=1 ";
                    return con.Query<RenLiZiYuanDangAnGuanLi>(sql).ToList();
                }
            });
        }

        public Task<FenYe<RenLiZiYuanDangAnGuanLi>> ShanChuFenYe(int size, int currentPage)
        {
            return Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection(conStr))
                {
                    DynamicParameters dynamicParameters = new DynamicParameters();
                    dynamicParameters.Add("@pageSize", size);
                    dynamicParameters.Add("@keyName", "huf_id");
                    dynamicParameters.Add("@tabelName", "human_file");
                    dynamicParameters.Add("@where", "u_roleID!=1 and check_status=1");
                    dynamicParameters.Add("@currentPage", currentPage);
                    dynamicParameters.Add("@lie", "*");
                    dynamicParameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    string sql = "exec proc_Fenye @pageSize, @keyName, @tabelName, @where, @currentPage, @lie, @rows out";
                    List<RenLiZiYuanDangAnGuanLi> list = conn.Query<RenLiZiYuanDangAnGuanLi>(sql, dynamicParameters).ToList();
                    int rows = dynamicParameters.Get<int>("@rows");
                    FenYe<RenLiZiYuanDangAnGuanLi> fenYe = new FenYe<RenLiZiYuanDangAnGuanLi>()
                    {
                        Rows = rows,
                        RenLi = list
                    };
                    return fenYe;
                }
            });
        }

        public Task<List<RenLiZiYuanDangAnGuanLi>> ShanChuFind(string? one, string? two, string? three, string? name, string? type, string? minCheng, string? time1, string? time2)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Query<RenLiZiYuanDangAnGuanLi>($"select * from human_file where delete_status=0 and u_roleID!=1 and check_status=1 and first_kind_name='{one}' and second_kind_name='{two}' and third_kind_name='{three}' and human_name='{name}' and human_major_kind_name='{type}' and human_major_name='{minCheng}' and regist_time between '{time1}' and '{time2}'").ToList();
                }
            });
        }

        public Task<int> UpdaBianGen(RenLiZiYuanDangAnGuanLi renLiZiYuanDangAnGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update human_file set human_name='{renLiZiYuanDangAnGuanLi.Human_name}', human_address='{renLiZiYuanDangAnGuanLi.Human_address}', human_postcode='{renLiZiYuanDangAnGuanLi.Human_postcode}', human_pro_designation='{renLiZiYuanDangAnGuanLi.Human_pro_designation}', human_telephone='{renLiZiYuanDangAnGuanLi.Human_telephone}',human_mobilephone='{renLiZiYuanDangAnGuanLi.Human_mobilephone}', human_bank='{renLiZiYuanDangAnGuanLi.Human_bank}', human_account='{renLiZiYuanDangAnGuanLi.Human_account}', human_qq='{renLiZiYuanDangAnGuanLi.Human_qq}', human_email='{renLiZiYuanDangAnGuanLi.Human_email}', human_hobby='{renLiZiYuanDangAnGuanLi.Human_hobby}', human_speciality='{renLiZiYuanDangAnGuanLi.Human_speciality}',human_sex='{renLiZiYuanDangAnGuanLi.Human_sex}', human_religion='{renLiZiYuanDangAnGuanLi.Human_religion}', human_party='{renLiZiYuanDangAnGuanLi.Human_party}', human_nationality='{renLiZiYuanDangAnGuanLi.Human_nationality}', human_race='{renLiZiYuanDangAnGuanLi.Human_race}', human_birthday='{renLiZiYuanDangAnGuanLi.Human_birthday}',human_birthplace='{renLiZiYuanDangAnGuanLi.Human_birthplace}', human_age='{renLiZiYuanDangAnGuanLi.Human_age}', human_educated_degree='{renLiZiYuanDangAnGuanLi.Human_educated_degree}', human_educated_years='{renLiZiYuanDangAnGuanLi.Human_educated_years}', human_educated_major='{renLiZiYuanDangAnGuanLi.Human_educated_major}',human_society_security_id='{renLiZiYuanDangAnGuanLi.Human_society_security_id}', human_id_card='{renLiZiYuanDangAnGuanLi.Human_id_card}', remark='{renLiZiYuanDangAnGuanLi.Remark}',salary_sum='{renLiZiYuanDangAnGuanLi.Salary_sum}', demand_salaray_sum='{renLiZiYuanDangAnGuanLi.Demand_salaray_sum}', paid_salary_sum='{renLiZiYuanDangAnGuanLi.Paid_salary_sum}',human_histroy_records='{renLiZiYuanDangAnGuanLi.Human_histroy_records}', human_family_membership='{renLiZiYuanDangAnGuanLi.Human_family_membership}',human_picture='{renLiZiYuanDangAnGuanLi.Human_picture}',check_status='{renLiZiYuanDangAnGuanLi.Check_status}',changer='{renLiZiYuanDangAnGuanLi.Changer}', change_time='{renLiZiYuanDangAnGuanLi.Change_time}' where huf_id='{renLiZiYuanDangAnGuanLi.Huf_id}'");
                }
            });
        }
        /// <summary>
		/// 这里是职工的复核事件，
		/// 1.复核通过后=>表示该职工已经正式入职。
		/// 2.需要将对应的薪酬信息添加到薪酬发放明细表中。
		/// </summary>
		/// <param name="renLiZiYuanDangAnGuanLi"></param>
		/// <returns></returns>
		public Task<int> Update(RenLiZiYuanDangAnGuanLi hf)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    //修改人力资源复核通过，正式入职，将发工资状态默认给False：未发工资
                    string sqlFuhe = $"update human_file set human_file_status='{hf.Human_file_status}',check_status='{hf.Check_status}',checker='{hf.Checker}',check_time='{hf.Check_time}',salary_grant_status='{false}' where huf_id='{hf.Huf_id}'";
                    /// 将该职工的薪酬信息添加到薪酬发放明细表中。
                    /// 1.先查询薪酬发放表中是否存在该三级机构，如果存在，则在人数上+1，反之，则新增一条薪酬发放信息
                    /// 2.再创建完薪酬发放信息后，并迭代新增我的薪酬发放详情
                    string sqlSgFind = @$"select * from salary_grant where [first_kind_id]='{hf.First_kind_id}' and [second_kind_id]='{hf.Second_kind_id}' and [third_kind_id]='{hf.Third_kind_id}'";
                    salary_grant? sg = con.Query<salary_grant>(sqlSgFind).FirstOrDefault();
                    //外部定义的薪酬发放标记ID，可能是修改的ID，可能是新增的ID
                    string sgid = "";
                    if (sg != null)
                    {
                        //存在这条机构的薪酬发放数据，修改这条薪酬发放的人数+1并修改总金额（金额强制类型转换）
                        string sqlSgUdp = @$"UPDATE [dbo].[salary_grant] SET [human_amount] = '{sg.human_amount + 1}',[salary_standard_sum] = '{(sg.salary_standard_sum + (decimal)hf.Demand_salaray_sum)}',[salary_paid_sum] = '{(sg.salary_paid_sum + (decimal)hf.Paid_salary_sum)}' WHERE [sgr_id]='{sg.sgr_id}'";
                        //修改薪酬发放表
                        int sgudpRes = con.Execute(sqlSgUdp);
                        //给标记ID赋值
                        sgid = sg.salary_grant_id;
                    }
                    else
                    {
                        //若不存在该条薪酬发放数据，新增这个机构的薪酬发放数据，给薪酬发放表自增ID
                        string sg_id = con.Query<string>("SELECT [salary_grant_id] FROM [dbo].[salary_grant] ORDER BY [sgr_id] DESC").FirstOrDefault();
                        string NewSg_id = NextNumber(sg_id);
                        //string registitme = hf.Regist_time.ToString("yyyy-MM-dd HH:mm:ss");
                        string sqlSgAdd = @$"INSERT INTO [dbo].[salary_grant] ([salary_grant_id],[salary_standard_id],[first_kind_id]
                        ,[first_kind_name],[second_kind_id],[second_kind_name],[third_kind_id],[third_kind_name],[human_amount],[salary_standard_sum]
                        ,[salary_paid_sum],[register],[regist_time]) VALUES ('{NewSg_id}','{hf.Salary_standard_id}','{hf.First_kind_id}','{hf.First_kind_name}'
                        ,'{hf.Second_kind_id}','{hf.Second_kind_name}' ,'{hf.Third_kind_id}','{hf.Third_kind_name}','1','{hf.Salary_sum}','{hf.Paid_salary_sum}'
                        ,'{hf.Register}','{hf.Regist_time}')";
                        //新增薪酬发放表
                        int sgaddRes = con.Execute(sqlSgAdd);
                        //给标记ID赋值
                        sgid = NewSg_id;
                    }
                    //新增薪酬发放详情明细,初始的奖励金额，绩效和应扣都为零，后续可以新增修改
                    string sqlSgdAdd = @$"INSERT INTO [dbo].[salary_grant_details] ([salary_grant_id],[human_id],[human_name]
                        ,[bouns_sum],[sale_sum],[deduct_sum],[salary_standard_sum],[salary_paid_sum])
                        VALUES ('{sgid}','{hf.Human_id}','{hf.Human_name}','{0}','{0}','{0}','{hf.Salary_sum}','{hf.Paid_salary_sum}')";
                    //新增发放明细表
                    int sgdaddRes = con.Execute(sqlSgdAdd);
                    return con.Execute(sqlFuhe);
                }
            });
        }
        /// 自动生成下一个薪酬发放ID
        public string NextNumber(string BaseNumber)
        {
            string NewNumber = "";//新值
            int InNumber = 1;//进位
            int PlaceValue;//位值
            char[] No = BaseNumber.ToCharArray();

            for (int i = BaseNumber.Length - 1; i >= 0; i--)
            {
                if (No[i] == '9' && InNumber == 1)
                {
                    InNumber = 1;
                    NewNumber = "0" + NewNumber;
                }
                else
                 if (InNumber == 1 && No[i] >= '0' && No[i] < '9')
                {
                    PlaceValue = Int32.Parse(No[i].ToString());
                    PlaceValue = (InNumber + PlaceValue);
                    InNumber = 0;
                    NewNumber = PlaceValue.ToString() + NewNumber;
                }
                else
                {
                    InNumber = 0;
                    NewNumber = No[i] + NewNumber;
                }
            }
            if (BaseNumber == NewNumber)
                NewNumber = "0000000001";
            return NewNumber;
        }
        public Task<int> UpdStatus(RenLiZiYuanDangAnGuanLi renLiZiYuanDangAnGuanLi)
        {
            return Task.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    return con.Execute($"update human_file set human_name='{renLiZiYuanDangAnGuanLi.Human_name}',delete_time='{renLiZiYuanDangAnGuanLi.Delete_time}',delete_status='{renLiZiYuanDangAnGuanLi.Delete_status}' where huf_id='{renLiZiYuanDangAnGuanLi.Huf_id}'");
                }
            });
        }

        public async Task<string> ZiDongID()
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                Task<string> human = Task.Run(() =>
                {
                    DynamicParameters dynamic = new DynamicParameters();
                    dynamic.Add("@danhao", "", dbType: DbType.String, direction: ParameterDirection.Output);
                    string sql = "exec [dbo].[pro_DangAnID] @danhao output";
                    var list = con.Query<RenLiZiYuanDangAnGuanLi>(sql, dynamic);
                    return dynamic.Get<string>("@danhao");

                });
                return await human;
            }
        }
    }
}
